/*
{
"状态":1,
"名称":"获取-cmd权限信息",
"作者":"陈泽椿",
"创建时间":"2018-10-29",
"功能":"获取-cmd权限信息",
"log": [
	{"date":"06-01" , "dev" :"hzhh" , "content" : ""}
]
}
*/
declare @sql varchar(max)
declare @cmd varchar(100),@classify varchar(100)
declare @where varchar(max)=''
declare @cmd_tree_key int
 
set @cmd = '{cmd}'
set @classify = '{classify}'
set @cmd_tree_key='{cmd_tree_key}'

if isnull(@cmd_tree_key,'')<>''
begin 
    if exists(select 1 from y_cmd_tree where tree_name='待分配' and cmd_tree_key=@cmd_tree_key)
    begin
		set @where=' And cmd_power_id=''assign'''
    end 
    else 
    begin
		set @where = '  And cmd_tree_key in (select cmd_tree_key from #T)'
	end 
end 

if (@classify <>'')
begin
	set @where += ' and classify='''+@classify+''''
end

if (@cmd <>'')
begin
	set @where += ' and (cmd_id like ''%'+@cmd+'%'' or cmd_name like ''%'+@cmd+'%'')'
end
set @sql = '
;WITH CT 
AS
(
	SELECT cmd_tree_key,father_key FROM y_cmd_tree WHERE cmd_tree_key='+convert(varchar(10),@cmd_tree_key)+'
	UNION ALL
    SELECT A.cmd_tree_key,A.father_key FROM CT INNER JOIN y_cmd_tree A ON CT.cmd_tree_key=A.father_key
)
SELECT cmd_tree_key into #T FROM CT

 
select cmd_key,cmd_id,cmd_power_id,cmd_name,remarks,classify,detail_name as name,convert(varchar(20),y_cmd.create_time,120)create_time,
convert(varchar(20),y_cmd.update_time,120)update_time,content from y_cmd,y_dict where 1=1 and classify=dict_id  {where} '
set @sql =replace(@sql,'{where}',@where)
exec(@sql)


 